package cn.edu.buaa.treehole.dao.implement;

import cn.edu.buaa.treehole.dao.SignatureDao;
import cn.edu.buaa.treehole.dao.exception.*;
import cn.edu.buaa.treehole.dao.util.DbAccess;
import cn.edu.buaa.treehole.dao.util.DbConnection;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/*
 * @author: Zhu Leyan
 */
@Repository
public class SignatureDaoImpl implements SignatureDao {

    private final DbConnection conn;

    private static final String insert = "INSERT INTO signature (pid, signature, name) " +
            "VALUES (%d, \"%s\", \"%s\")";

    private static final String selectPidSig = "SELECT name FROM signature WHERE pid=%d AND signature=\"%s\"";

    private static final String selectPidName = "SELECT name FROM signature WHERE pid=%d AND name=\"%s\"";

    public SignatureDaoImpl() {
        this.conn = DbConnection.getInstance(DbAccess.url, DbAccess.usr, DbAccess.pwd);
    }

    private Statement createStatement() throws SQLException, DaoException {
        //if (this.conn.isClosed() && this.conn.open()) {
        //    throw new DaoException("Connection to database cannot be setup.");
        //}
        Connection connection = this.conn.getConnection();
        if (connection == null) {
            throw new DaoException("Connection setup successfully, but we " +
                    "got a null connection instance.");
        }
        return connection.createStatement();
    }

    @Override
    public String getName(long pid, String signature) throws DaoException {
        try {
            Statement statement = this.createStatement();
            ResultSet rs = statement.executeQuery(String.format(selectPidSig, pid, signature));
            if (rs == null) {
                statement.close();
                throw new DaoException("Unknown error.");
            }
            if (rs.next()) {
                String name = rs.getString("name");
                rs.close();
                statement.close();
                return name;
            } else {
                rs.close();
                statement.close();
                throw new SignatureNotExistException("Signature " + signature + " is not existing.");
            }
        } catch (SQLException e) {
            throw new DaoException(
                    "This Exception is caused by SQLException: " + e.getCause() +
                            ", SQL State: " + e.getSQLState() +
                            ", Error Code: " + e.getErrorCode()
            );
        }
    }

    @Override
    public void addName(long pid, String signature, String name) throws DaoException {
        try {
            Statement statement = this.createStatement();
            ResultSet rs = statement.executeQuery(String.format(selectPidSig, pid, signature));
            if (rs == null) {
                statement.close();
                System.out.println("Unknown error.");
                throw new DaoException("Unknown error.");
            }
            if (rs.next()) {
                rs.close();
                statement.close();
                System.out.println("Signature " + signature + " is existing.");
                throw new SignatureHasExistException("Signature " + signature + " is existing.");
            }
            if (statement.executeUpdate(String.format(
                   insert, pid, signature, name
            )) == 0) {
                rs.close();
                statement.close();
                System.out.println("Insertion into database failed.");
                throw new DaoException("Insertion into database failed.");
            }
            rs.close();
            statement.close();
        } catch (SQLException e) {
            throw new DaoException(
                    "This Exception is caused by SQLException: " + e.getCause() +
                            ", SQL State: " + e.getSQLState() +
                            ", Error Code: " + e.getErrorCode()
            );
        }
    }

    @Override
    public boolean containName(long pid, String name) throws DaoException {
        try {
            Statement statement = this.createStatement();
            ResultSet rs = statement.executeQuery(String.format(selectPidName, pid, name));
            if (rs == null) {
                statement.close();
                throw new DaoException("Unknown error.");
            }
            boolean ret = rs.next();
            rs.close();
            statement.close();
            return ret;
        } catch (SQLException e) {
            throw new DaoException(
                    "This Exception is caused by SQLException: " + e.getCause() +
                            ", SQL State: " + e.getSQLState() +
                            ", Error Code: " + e.getErrorCode()
            );
        }
    }

    @Override
    public boolean containSignature(long pid, String signature) throws DaoException {
        try {
            Statement statement = this.createStatement();
            ResultSet rs = statement.executeQuery(String.format(selectPidSig, pid, signature));
            if (rs == null) {
                statement.close();
                throw new DaoException("Unknown error.");
            }
            boolean ret = rs.next();
            rs.close();
            statement.close();
            return ret;
        } catch (SQLException e) {
            throw new DaoException(
                    "This Exception is caused by SQLException: " + e.getCause() +
                            ", SQL State: " + e.getSQLState() +
                            ", Error Code: " + e.getErrorCode()
            );
        }
    }

    @Override
    public int getCount(long pid) throws DaoException {
        try {
            Statement statement = this.createStatement();
            ResultSet rs = statement.executeQuery("SELECT * FROM signature WHERE pid=" + pid);
            rs.last();
            int ret = rs.getRow();
            rs.close();
            statement.close();
            return ret;
        } catch (SQLException e) {
            throw new DaoException(
                    "This Exception is caused by SQLException: " + e.getCause() +
                            ", SQL State: " + e.getSQLState() +
                            ", Error Code: " + e.getErrorCode()
            );
        }
    }
}
